<!DOCTYPE html>
<html lang="zh-CN">
<head>
  <meta charset="UTF-8">
<meta name="viewport" content="width=device-width">
<meta name="theme-color" content="#222">
<meta name="generator" content="Hexo 5.4.0">


  <link rel="apple-touch-icon" sizes="180x180" href="/images/apple-touch-icon-next.png">
  <link rel="icon" type="image/png" sizes="32x32" href="/images/favicon-32x32-next.png">
  <link rel="icon" type="image/png" sizes="16x16" href="/images/favicon-16x16-next.png">
  <link rel="mask-icon" href="/images/logo.svg" color="#222">

<link rel="stylesheet" href="/css/main.css">



<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/@fortawesome/fontawesome-free@5.15.4/css/all.min.css" integrity="sha256-mUZM63G8m73Mcidfrv5E+Y61y7a12O5mW4ezU3bxqW4=" crossorigin="anonymous">
  <link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/animate.css@3.1.1/animate.min.css" integrity="sha256-PR7ttpcvz8qrF57fur/yAx1qXMFJeJFiA6pSzWi0OIE=" crossorigin="anonymous">

<script class="next-config" data-name="main" type="application/json">{"hostname":"zhengmingpei.gitee.io","root":"/","images":"/images","scheme":"Pisces","darkmode":false,"version":"8.8.1","exturl":false,"sidebar":{"position":"left","display":"post","padding":18,"offset":12},"copycode":false,"bookmark":{"enable":false,"color":"#222","save":"auto"},"mediumzoom":false,"lazyload":false,"pangu":false,"comments":{"style":"tabs","active":null,"storage":true,"lazyload":false,"nav":null},"stickytabs":false,"motion":{"enable":true,"async":false,"transition":{"post_block":"fadeIn","post_header":"fadeInDown","post_body":"fadeInDown","coll_header":"fadeInLeft","sidebar":"fadeInUp"}},"prism":false,"i18n":{"placeholder":"搜索...","empty":"没有找到任何搜索结果：${query}","hits_time":"找到 ${hits} 个搜索结果（用时 ${time} 毫秒）","hits":"找到 ${hits} 个搜索结果"}}</script><script src="/js/config.js"></script>
<meta name="description" content="其他查表操作NULLConceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here: SELEC">
<meta property="og:type" content="article">
<meta property="og:title" content="MySQL学习笔记4">
<meta property="og:url" content="https://zhengmingpei.gitee.io/2016/09/28/MySQL-Note4/index.html">
<meta property="og:site_name" content="郑明培的个人博客">
<meta property="og:description" content="其他查表操作NULLConceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values. To test for NULL, use the IS NULL and IS NOT NULL operators, as shown here: SELEC">
<meta property="og:locale" content="zh_CN">
<meta property="og:image" content="https://zhengmingpei.gitee.io/2016/09/28/MySQL-Note4/img_innerjoin.gif">
<meta property="og:image" content="https://zhengmingpei.gitee.io/2016/09/28/MySQL-Note4/img_leftjoin.gif">
<meta property="og:image" content="https://zhengmingpei.gitee.io/2016/09/28/MySQL-Note4/img_rightjoin.gif">
<meta property="article:published_time" content="2016-09-28T07:00:00.000Z">
<meta property="article:modified_time" content="2021-11-28T04:48:19.983Z">
<meta property="article:author" content="zhengmingpei">
<meta property="article:tag" content="Mysql">
<meta property="article:tag" content="数据库">
<meta name="twitter:card" content="summary">
<meta name="twitter:image" content="https://zhengmingpei.gitee.io/2016/09/28/MySQL-Note4/img_innerjoin.gif">


<link rel="canonical" href="https://zhengmingpei.gitee.io/2016/09/28/MySQL-Note4/">



<script class="next-config" data-name="page" type="application/json">{"sidebar":"","isHome":false,"isPost":true,"lang":"zh-CN","comments":true,"permalink":"https://zhengmingpei.gitee.io/2016/09/28/MySQL-Note4/","path":"2016/09/28/MySQL-Note4/","title":"MySQL学习笔记4"}</script>

<script class="next-config" data-name="calendar" type="application/json">""</script>
<title>MySQL学习笔记4 | 郑明培的个人博客</title>
  




  <noscript>
    <link rel="stylesheet" href="/css/noscript.css">
  </noscript>
</head>

<body itemscope itemtype="http://schema.org/WebPage" class="use-motion">
  <div class="headband"></div>

  <main class="main">
    <header class="header" itemscope itemtype="http://schema.org/WPHeader">
      <div class="header-inner"><div class="site-brand-container">
  <div class="site-nav-toggle">
    <div class="toggle" aria-label="切换导航栏" role="button">
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
        <span class="toggle-line"></span>
    </div>
  </div>

  <div class="site-meta">

    <a href="/" class="brand" rel="start">
      <i class="logo-line"></i>
      <h1 class="site-title">郑明培的个人博客</h1>
      <i class="logo-line"></i>
    </a>
  </div>

  <div class="site-nav-right">
    <div class="toggle popup-trigger">
    </div>
  </div>
</div>



<nav class="site-nav">
  <ul class="main-menu menu">
        <li class="menu-item menu-item-home"><a href="/" rel="section"><i class="home fa-fw"></i>首页</a></li>
        <li class="menu-item menu-item-archives"><a href="/archives" rel="section"><i class="archive fa-fw"></i>归档</a></li>
        <li class="menu-item menu-item-about"><a href="/about" rel="section"><i class="user fa-fw"></i>关于</a></li>
        <li class="menu-item menu-item-categories"><a href="/categories" rel="section"><i class="th fa-fw"></i>分类</a></li>
        <li class="menu-item menu-item-tags"><a href="/tags" rel="section"><i class="tags fa-fw"></i>标签</a></li>
  </ul>
</nav>




</div>
        
  
  <div class="toggle sidebar-toggle" role="button">
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
    <span class="toggle-line"></span>
  </div>

  <aside class="sidebar">

    <div class="sidebar-inner sidebar-nav-active sidebar-toc-active">
      <ul class="sidebar-nav">
        <li class="sidebar-nav-toc">
          文章目录
        </li>
        <li class="sidebar-nav-overview">
          站点概览
        </li>
      </ul>

      <div class="sidebar-panel-container">
        <!--noindex-->
        <div class="post-toc-wrap sidebar-panel">
            <div class="post-toc animated"><ol class="nav"><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%85%B6%E4%BB%96%E6%9F%A5%E8%A1%A8%E6%93%8D%E4%BD%9C"><span class="nav-number">1.</span> <span class="nav-text">其他查表操作</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#NULL"><span class="nav-number">1.1.</span> <span class="nav-text">NULL</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#%E8%AE%A1%E7%AE%97%E6%BB%A1%E8%B6%B3%E6%9D%A1%E4%BB%B6%E7%9A%84%E8%A1%8C%E6%95%B0"><span class="nav-number">1.2.</span> <span class="nav-text">计算满足条件的行数</span></a></li><li class="nav-item nav-level-3"><a class="nav-link" href="#JOIN"><span class="nav-number">1.3.</span> <span class="nav-text">JOIN</span></a></li></ol></li><li class="nav-item nav-level-2"><a class="nav-link" href="#%E5%B8%B8%E8%A7%81%E6%9F%A5%E8%A1%A8%E8%8C%83%E4%BE%8B"><span class="nav-number">2.</span> <span class="nav-text">常见查表范例</span></a><ol class="nav-child"><li class="nav-item nav-level-3"><a class="nav-link" href="#%E6%9F%A5%E6%89%BE%E6%9E%81%E5%80%BC"><span class="nav-number">2.1.</span> <span class="nav-text">查找极值</span></a></li></ol></li></ol></div>
        </div>
        <!--/noindex-->

        <div class="site-overview-wrap sidebar-panel">
          <div class="site-author site-overview-item animated" itemprop="author" itemscope itemtype="http://schema.org/Person">
  <p class="site-author-name" itemprop="name">zhengmingpei</p>
  <div class="site-description" itemprop="description"></div>
</div>
<div class="site-state-wrap site-overview-item animated">
  <nav class="site-state">
      <div class="site-state-item site-state-posts">
        <a href="/archives">
          <span class="site-state-item-count">36</span>
          <span class="site-state-item-name">日志</span>
        </a>
      </div>
      <div class="site-state-item site-state-categories">
          <a href="/categories">
        <span class="site-state-item-count">4</span>
        <span class="site-state-item-name">分类</span></a>
      </div>
      <div class="site-state-item site-state-tags">
          <a href="/tags">
        <span class="site-state-item-count">41</span>
        <span class="site-state-item-name">标签</span></a>
      </div>
  </nav>
</div>
  <div class="links-of-author site-overview-item animated">
      <span class="links-of-author-item">
        <a href="https://github.com/zhengmingpei" title="GitHub → https:&#x2F;&#x2F;github.com&#x2F;zhengmingpei" rel="noopener" target="_blank"><i class="github fa-fw"></i>GitHub</a>
      </span>
      <span class="links-of-author-item">
        <a href="https://gitee.com/zhengmingpei" title="gitee → https:&#x2F;&#x2F;gitee.com&#x2F;zhengmingpei" rel="noopener" target="_blank">gitee</a>
      </span>
      <span class="links-of-author-item">
        <a href="https://space.bilibili.com/32918983" title="bilibili → https:&#x2F;&#x2F;space.bilibili.com&#x2F;32918983" rel="noopener" target="_blank">bilibili</a>
      </span>
  </div>



        </div>
      </div>
    </div>
  </aside>
  <div class="sidebar-dimmer"></div>


    </header>

    
  <div class="back-to-top" role="button" aria-label="返回顶部">
    <i class="fa fa-arrow-up"></i>
    <span>0%</span>
  </div>

<noscript>
  <div class="noscript-warning">Theme NexT works best with JavaScript enabled</div>
</noscript>


    <div class="main-inner post posts-expand">


  


<div class="post-block">
  
  

  <article itemscope itemtype="http://schema.org/Article" class="post-content" lang="zh-CN">
    <link itemprop="mainEntityOfPage" href="https://zhengmingpei.gitee.io/2016/09/28/MySQL-Note4/">

    <span hidden itemprop="author" itemscope itemtype="http://schema.org/Person">
      <meta itemprop="image" content="/images/avatar.gif">
      <meta itemprop="name" content="zhengmingpei">
      <meta itemprop="description" content="">
    </span>

    <span hidden itemprop="publisher" itemscope itemtype="http://schema.org/Organization">
      <meta itemprop="name" content="郑明培的个人博客">
    </span>
      <header class="post-header">
        <h1 class="post-title" itemprop="name headline">
          MySQL学习笔记4
        </h1>

        <div class="post-meta-container">
          <div class="post-meta">
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-calendar"></i>
      </span>
      <span class="post-meta-item-text">发表于</span>

      <time title="创建时间：2016-09-28 15:00:00" itemprop="dateCreated datePublished" datetime="2016-09-28T15:00:00+08:00">2016-09-28</time>
    </span>
      <span class="post-meta-item">
        <span class="post-meta-item-icon">
          <i class="far fa-calendar-check"></i>
        </span>
        <span class="post-meta-item-text">更新于</span>
        <time title="修改时间：2021-11-28 12:48:19" itemprop="dateModified" datetime="2021-11-28T12:48:19+08:00">2021-11-28</time>
      </span>
    <span class="post-meta-item">
      <span class="post-meta-item-icon">
        <i class="far fa-folder"></i>
      </span>
      <span class="post-meta-item-text">分类于</span>
        <span itemprop="about" itemscope itemtype="http://schema.org/Thing">
          <a href="/categories/%E5%9C%A8%E5%8C%97%E4%BA%AC/" itemprop="url" rel="index"><span itemprop="name">在北京</span></a>
        </span>
    </span>

  
</div>

        </div>
      </header>

    
    
    
    <div class="post-body" itemprop="articleBody">
        <h2 id="其他查表操作"><a href="#其他查表操作" class="headerlink" title="其他查表操作"></a>其他查表操作</h2><h3 id="NULL"><a href="#NULL" class="headerlink" title="NULL"></a>NULL</h3><p>Conceptually, NULL means “a missing unknown value” and it is treated somewhat differently from other values.</p>
<p>To test for NULL, use the <code>IS NULL</code> and <code>IS NOT NULL</code> operators, as shown here:</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="number">1</span> <span class="keyword">IS</span> <span class="keyword">NULL</span>, <span class="number">1</span> <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span>;</span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+---------------+</span></span><br><span class="line"><span class="operator">|</span> <span class="number">1</span> <span class="keyword">IS</span> <span class="keyword">NULL</span> <span class="operator">|</span> <span class="number">1</span> <span class="keyword">IS</span> <span class="keyword">NOT</span> <span class="keyword">NULL</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+---------------+</span></span><br><span class="line"><span class="operator">|</span></span><br><span class="line"><span class="number">0</span> <span class="operator">|</span></span><br><span class="line"><span class="number">1</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">-----------+---------------+</span></span><br></pre></td></tr></table></figure>

<p>You cannot use arithmetic comparison operators such as <code>=</code>, <code>&lt;</code>, or <code>&lt;&gt;</code> to test for NULL.</p>
<span id="more"></span>

<h3 id="计算满足条件的行数"><a href="#计算满足条件的行数" class="headerlink" title="计算满足条件的行数"></a>计算满足条件的行数</h3><p>使用<code>COUNT(*)</code>计算行数，结合<code>GROUP BY</code>可以实现更多自定义筛选，比如：</p>
<p>对于表student：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="operator">+</span><span class="comment">--------+------+------------+</span></span><br><span class="line"><span class="operator">|</span> name   <span class="operator">|</span> sex  <span class="operator">|</span> birth      <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------+------+------------+</span></span><br><span class="line"><span class="operator">|</span> Bob    <span class="operator">|</span> m    <span class="operator">|</span> <span class="number">1989</span><span class="number">-08</span><span class="number">-31</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Wesley <span class="operator">|</span> m    <span class="operator">|</span> <span class="number">1990</span><span class="number">-08</span><span class="number">-31</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Ana    <span class="operator">|</span> f    <span class="operator">|</span> <span class="number">1991</span><span class="number">-01</span><span class="number">-02</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Ana    <span class="operator">|</span> f    <span class="operator">|</span> <span class="number">1999</span><span class="number">-09</span><span class="number">-28</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> Linus  <span class="operator">|</span> <span class="keyword">NULL</span> <span class="operator">|</span> <span class="number">1983</span><span class="number">-09</span><span class="number">-28</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">--------+------+------------+</span></span><br></pre></td></tr></table></figure>

<p>可以计算不同性别以及未填写性别信息的学生数量：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> sex, <span class="built_in">COUNT</span>(<span class="operator">*</span>) <span class="keyword">AS</span> count <span class="keyword">FROM</span> student <span class="keyword">GROUP</span> <span class="keyword">BY</span> sex;</span><br><span class="line"><span class="operator">+</span><span class="comment">------+-------+</span></span><br><span class="line"><span class="operator">|</span> sex  <span class="operator">|</span> count <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">------+-------+</span></span><br><span class="line"><span class="operator">|</span> <span class="keyword">NULL</span> <span class="operator">|</span>     <span class="number">1</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> f    <span class="operator">|</span>     <span class="number">2</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">|</span> m    <span class="operator">|</span>     <span class="number">2</span> <span class="operator">|</span></span><br><span class="line"><span class="operator">+</span><span class="comment">------+-------+</span></span><br></pre></td></tr></table></figure>

<p>(In this output, NULL indicates that the sex is unknown.)</p>
<h3 id="JOIN"><a href="#JOIN" class="headerlink" title="JOIN"></a>JOIN</h3><p>JOIN是将多张表进行合并的方法，主要有 <strong>Inner join</strong>和 <strong>Outer join</strong>，详情见维基百科<a target="_blank" rel="noopener" href="https://en.wikipedia.org/wiki/Join_%28SQL%29">这里</a>。</p>
<p>另外，还有一种<strong>Self-join</strong>比较特殊，是一张表的2份拷贝做 <strong>Inner join</strong>。</p>
<p><strong>Inner join</strong>会对两张表做笛卡尔积。</p>
<p><strong>Outer join</strong>包括<strong>LEFT JOIN</strong>和<strong>RIGHT JOIN</strong>，以上3种JOIN的解释可见<a target="_blank" rel="noopener" href="http://www.runoob.com/mysql/mysql-join.html">这里</a>，有图如下：</p>
<p><img src="/2016/09/28/MySQL-Note4/img_innerjoin.gif" alt="img_innerjoin.gif"><br><img src="/2016/09/28/MySQL-Note4/img_leftjoin.gif" alt="img_leftjoin.gif"><br><img src="/2016/09/28/MySQL-Note4/img_rightjoin.gif" alt="img_rightjoin.gif"></p>
<h2 id="常见查表范例"><a href="#常见查表范例" class="headerlink" title="常见查表范例"></a>常见查表范例</h2><h3 id="查找极值"><a href="#查找极值" class="headerlink" title="查找极值"></a>查找极值</h3><p>使用<code>MAX()</code>和<code>MIN()</code>，比如对于上面的表student，可以取日期的最大值，最小值和各自的完整行信息如下：</p>
<figure class="highlight sql"><table><tr><td class="code"><pre><span class="line"><span class="keyword">SELECT</span> <span class="built_in">MAX</span>(birth) <span class="keyword">FROM</span> student ;</span><br><span class="line"><span class="keyword">SELECT</span> <span class="built_in">MAX</span>(birth) <span class="keyword">FROM</span> student ;</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> student <span class="keyword">WHERE</span> birth<span class="operator">=</span>(<span class="keyword">SELECT</span> <span class="built_in">MAX</span>(birth) <span class="keyword">FROM</span> student);</span><br><span class="line"><span class="keyword">SELECT</span> <span class="operator">*</span> <span class="keyword">FROM</span> student <span class="keyword">WHERE</span> birth<span class="operator">=</span>(<span class="keyword">SELECT</span> <span class="built_in">MIN</span>(birth) <span class="keyword">FROM</span> student);</span><br></pre></td></tr></table></figure>


    </div>

    
    
    

    <footer class="post-footer">
          <div class="post-tags">
              <a href="/tags/Mysql/" rel="tag"># Mysql</a>
              <a href="/tags/%E6%95%B0%E6%8D%AE%E5%BA%93/" rel="tag"># 数据库</a>
          </div>

        

          <div class="post-nav">
            <div class="post-nav-item">
                <a href="/2016/09/27/MySQL-Note3/" rel="prev" title="MySQL学习笔记3">
                  <i class="fa fa-chevron-left"></i> MySQL学习笔记3
                </a>
            </div>
            <div class="post-nav-item">
                <a href="/2016/09/30/ShellNote1/" rel="next" title="Shell命令备忘1">
                  Shell命令备忘1 <i class="fa fa-chevron-right"></i>
                </a>
            </div>
          </div>
    </footer>
  </article>
</div>






</div>
  </main>

  <footer class="footer">
    <div class="footer-inner">


<div class="copyright">
  &copy; 
  <span itemprop="copyrightYear">2021</span>
  <span class="with-love">
    <i class="fa fa-heart"></i>
  </span>
  <span class="author" itemprop="copyrightHolder">zhengmingpei</span>
</div>
  <div class="powered-by">由 <a href="https://hexo.io/" rel="noopener" target="_blank">Hexo</a> & <a href="https://theme-next.js.org/pisces/" rel="noopener" target="_blank">NexT.Pisces</a> 强力驱动
  </div>

    </div>
  </footer>

  
  <script src="https://cdn.jsdelivr.net/npm/animejs@3.2.1/lib/anime.min.js" integrity="sha256-XL2inqUJaslATFnHdJOi9GfQ60on8Wx1C2H8DYiN1xY=" crossorigin="anonymous"></script>
<script src="/js/comments.js"></script><script src="/js/utils.js"></script><script src="/js/motion.js"></script><script src="/js/next-boot.js"></script>

  





  





</body>
</html>
